OLE problem: Microsoft Office Excel cannot paste the data.

This code gives me the error "OLE problem: Microsoft Office Excel cannot paste the data." (from the print statement):

if (oleIsObject (o))
{
    string s = richTextWithOle (o."Object Text")
        RichText rtf
        for rtf in s do
        {
                if (rtf.isOle)
                {
                        EmbeddedOleObject ole = rtf.getEmbeddedOle
                        oleCopy (ole)
 
                        OleAutoArgs autoArgs = create
                        clear (autoArgs)
                        put (autoArgs, row)
                        put (autoArgs, col)
                        oleGet (objWorksheet, "Cells", autoArgs, objCell) // select cell
 
                        clear (autoArgs)
                        put (autoArgs, objCell)
                        string res = oleMethod (objWorksheet, "Paste", autoArgs)
                        delete autoArgs
                        if (res != "") print identifier (o) " " res "\n"
                }
        }
}

You can assume I have an open instance of Excel and can export text to cells successfully. Unfortunately, I'm having a terrible time exporting OLE objects.

Any ideas why I'm getting this error?

SystemAdmin - Tue Mar 02 11:50:48 EST 2010

Re: OLE problem: Microsoft Office Excel cannot paste the data.
SystemAdmin - Tue Mar 02 12:09:14 EST 2010

I apologize for the double post. I'd remove it, but can't seem to figure out how...

Re: OLE problem: Microsoft Office Excel cannot paste the data.
Mathias Mamsch - Tue Mar 02 19:22:11 EST 2010

Hmm that is strange, while I have no problems pasting an OLE object I cannot get it to paste at a special cell. It always gets pasted at Cells(1,1)... but it does not give me an OLE error ... No idea why ... Did you try the pasteSpecial method? Does the code below give you the same OLE error, when you select an object with an OLE object?

Best regards, Mathias

Object o = current
 
OleAutoObj wbs 
OleAutoObj wb
OleAutoObj ws
OleAutoObj objCell
 
OleAutoArgs args = create
// create an excel
OleAutoObj ex = oleCreateAutoObject "Excel.Application"
 
// get workbooks handle
oleGet (ex, "Workbooks", wbs) 
// add a workbook
oleMethod (wbs, "Add", args, wb)
// make excel visible
olePut(ex, "Visible", true)
// Get first worksheet
put(args, 1)
oleGet (ex, "Worksheets", args, ws) 
 
// your code now 
if (oleIsObject (o))
{
    string s = richTextWithOle (o."Object Text")
        RichText rtf
        for rtf in s do
        {
                if (rtf.isOle)
                {
                        EmbeddedOleObject ole = rtf.getEmbeddedOle
                        oleCopy (ole)
                        
                        clear args
                        put (args, 5)
                        put (args, 5)
                        oleGet (ws, "Cells", args, objCell) // select cell
 
                        clear args
                        put (args, objCell)
 
                        oleMethod (objCell, "Select")
 
                        clear args
                        put (args, objCell) 
 
                        // does not work either
                        // put (args, "Destination", objCell)
 
                        print "Pasting!"
                        print oleMethod (ws, "Paste",args)
                        
                }
        }
}

Re: OLE problem: Microsoft Office Excel cannot paste the data.
SystemAdmin - Wed Mar 03 09:37:19 EST 2010

Mathias Mamsch - Tue Mar 02 19:22:11 EST 2010

Object o = current
 
OleAutoObj wbs 
OleAutoObj wb
OleAutoObj ws
OleAutoObj objCell
 
OleAutoArgs args = create
// create an excel
OleAutoObj ex = oleCreateAutoObject "Excel.Application"
 
// get workbooks handle
oleGet (ex, "Workbooks", wbs) 
// add a workbook
oleMethod (wbs, "Add", args, wb)
// make excel visible
olePut(ex, "Visible", true)
// Get first worksheet
put(args, 1)
oleGet (ex, "Worksheets", args, ws) 
 
// your code now 
if (oleIsObject (o))
{
    string s = richTextWithOle (o."Object Text")
        RichText rtf
        for rtf in s do
        {
                if (rtf.isOle)
                {
                        EmbeddedOleObject ole = rtf.getEmbeddedOle
                        oleCopy (ole)
                        
                        clear args
                        put (args, 5)
                        put (args, 5)
                        oleGet (ws, "Cells", args, objCell) // select cell
 
                        clear args
                        put (args, objCell)
 
                        oleMethod (objCell, "Select")
 
                        clear args
                        put (args, objCell) 
 
                        // does not work either
                        // put (args, "Destination", objCell)
 
                        print "Pasting!"
                        print oleMethod (ws, "Paste",args)
                        
                }
        }
}

I used the "excelPasteOLEObjectOLE" function from "Enhanced Export To Excel.dxl" which you can get at the link below. That worked for me. Haven't really looked further into why my original code was getting an error. Also, there are functions in "Enhanced Export To Excel.dxl" that will resize the OLE object and column widths to fit the object into a single cell, which is nice for my format.

Link: http://www.baselinesinc.com/?page_id=11&filerepoaction=showpost&filepost_id=10

Re: OLE problem: Microsoft Office Excel cannot paste the data.
Ben_Sharples - Mon Mar 08 07:56:41 EST 2010

Mathias Mamsch - Tue Mar 02 19:22:11 EST 2010

Object o = current
 
OleAutoObj wbs 
OleAutoObj wb
OleAutoObj ws
OleAutoObj objCell
 
OleAutoArgs args = create
// create an excel
OleAutoObj ex = oleCreateAutoObject "Excel.Application"
 
// get workbooks handle
oleGet (ex, "Workbooks", wbs) 
// add a workbook
oleMethod (wbs, "Add", args, wb)
// make excel visible
olePut(ex, "Visible", true)
// Get first worksheet
put(args, 1)
oleGet (ex, "Worksheets", args, ws) 
 
// your code now 
if (oleIsObject (o))
{
    string s = richTextWithOle (o."Object Text")
        RichText rtf
        for rtf in s do
        {
                if (rtf.isOle)
                {
                        EmbeddedOleObject ole = rtf.getEmbeddedOle
                        oleCopy (ole)
                        
                        clear args
                        put (args, 5)
                        put (args, 5)
                        oleGet (ws, "Cells", args, objCell) // select cell
 
                        clear args
                        put (args, objCell)
 
                        oleMethod (objCell, "Select")
 
                        clear args
                        put (args, objCell) 
 
                        // does not work either
                        // put (args, "Destination", objCell)
 
                        print "Pasting!"
                        print oleMethod (ws, "Paste",args)
                        
                }
        }
}

Mathias,

looking at your code, args is only set to a singular integer. Cell references have both Column and Row settings.

when getting the ObjCell reference I Use the following code.

string cellref = "A" 1 ""
clear args
put (args, cellref)
oleGet (objSheet, "Range", args, objCell)